namespace Daemon.Service.Grid
{
    using Daemon.Model;
    using Daemon.Service.Contract;
    using Microsoft.EntityFrameworkCore;
    using Daemon.Data.Substructure.Framework;
    using Daemon.Model.Entities;
    using Daemon.Reflection;
    using Dynamitey;
    using Daemon.Infrustructure.EF.Executor;
    using Daemon.Data.Substructure.Enums;
    using Daemon.Data.Infrastructure.FrameWork;
    using Daemon.Data.Infrastructure.FrameWork.Query;
    using Microsoft.EntityFrameworkCore.Metadata;
    using System;
    using System.Collections.Concurrent;
    using System.Collections.Generic;
    using System.Data;
    using System.Dynamic;
    using System.Linq;
    using System.Linq.Expressions;
    using System.Reflection;
    using System.Text.RegularExpressions;
    using System.Threading.Tasks;
    using Newtonsoft.Json;
    using gudusoft.gsqlparser.nodes;
    using Daemon.Data.Infrastructure.Auth;
    using Daemon.Repository.Contract;
    public abstract class BaseGridService<TEntity, TContext, TIdentity> : IBaseGridService<TEntity, TContext, TIdentity>
         where TEntity : class, IGridEntity<TIdentity>, new()
         where TContext : ApiDBContent, new()
    {
        private const string _verifyOnlySql = "1 <> 1";

        private TContext _context;

        public bool IsVerify { protected get; set; }

        public ExportCallStackType CallStack { protected get; set; } = ExportCallStackType.Default;

        protected IDelimitedListParser DelimitedListParser { get; }

        private List<string> _entityPropertyNames;

        protected List<string> EntityPropertyNames
        {
            get
            {
                return _entityPropertyNames ?? (_entityPropertyNames = typeof(TEntity).GetProperties().Select(r => r.Name).ToList());
            }
        }

        protected abstract string SQLQuery { get; }

        protected abstract Expression<Func<TEntity, TIdentity>> GetId { get; }

        protected virtual List<string> NeedField => new List<string>();

        protected virtual List<BaseStaticFilter<TContext>> StaticFilters => new List<BaseStaticFilter<TContext>>();

        protected abstract string PrimaryKeyName { get; }

        public BaseGridService(TContext context, IDelimitedListParser delimitedListParser)
        {
            _context = context;
            DelimitedListParser = delimitedListParser;
        }

        protected virtual IQueryable<TEntity> CreateQueryable(short databaseId, TContext context, FilterSet filterSet)
        {
            return _context.Set<TEntity>().AsNoTracking();
        }

        public virtual string GetClauseFromFilter(FilterSet filterSet)
        {
            return GetClauseFromFilter(filterSet, _context);
        }

        public virtual List<SortItem> GetDefaultSortItems()
        {
            return new List<SortItem>() { new SortItem() { Name = PrimaryKeyName, Direction = SortDirection.Ascending } };
        }

        public virtual object Aggregate(
        AggregateItem aggregateItem,
        FilterSet filterSet,
        string gridFilterClause,
        List<int> includeOnlyIds,
        List<int> excludeAnyIds,
        bool? isQuickSearch = false,
        Func<IQueryable<TEntity>, IQueryable<TEntity>> filterFunc = null,
        bool retrievePropertyInfo = false)
        {
            var queryable = ApplyFilters(gridFilterClause, includeOnlyIds, excludeAnyIds, filterSet, _context, isQuickSearch);

            var entities = filterFunc == null ? queryable : filterFunc(queryable);
            var filterItemNames = new List<string> { aggregateItem.FieldName };
            return entities.Aggregate(aggregateItem, retrievePropertyInfo);
        }

        public List<BaseStaticFilter<TContext>> GetStaticFilters()
        {
            return StaticFilters;
        }

        public IQueryable<TEntity> GetByFilter(int? filterId)
        {
            var whereClause = string.Empty;
            if (!filterId.HasValue)
            {
                return GetByFilterClause(whereClause, _context);
            }

            return GetByFilterClause(whereClause, _context);
        }

        public Tuple<object, PropertyInfo> Aggregate(
            AggregateItem aggregateItem,
            FilterSet filterSet,
            string gridFilterClause,
            List<int> includeOnlyIds,
            List<int> excludeAnyIds)
        {
            return (Tuple<object, PropertyInfo>)Aggregate(aggregateItem, filterSet, gridFilterClause, includeOnlyIds, excludeAnyIds, false, null, true);
        }

        public SearchResultIds<TIdentity> Ids(IEnumerable<ISortItem> sortItems, FilterSet filterSet, string gridFilterClause, List<int> includeOnlyIds, List<int> excludeAnyIds, List<string> fields, bool? isQuickSearch = false)
        {
            try
            {
                var queryable = ApplyFilters(gridFilterClause, includeOnlyIds, excludeAnyIds, filterSet, fields, _context, isQuickSearch);
                var sortItemNames = sortItems?.Select(r => r.Name).ToList();
                var sortIDs = queryable.Sort(sortItems).Select(GetId).ToList();

                return new SearchResultIds<TIdentity>(sortIDs);
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        public List<dynamic> Search(
            List<string> fields,
            List<int> includeOnlyIds,
            bool isExpandoObject)
        {
            var entities = Search(fields, null, null, null, includeOnlyIds, null, null, null, false);
            return ExtendDataEntities(entities.Entities, fields, out var type, out _, isExpandoObject, _context, null, new FilterSet()).ToList();
        }

        public IQueryable<TEntity> GetByFilterClause(string gridFilterClause, TContext context = null)
        {
            context = context ?? _context;
            var queryable = CreateQueryable(context, null);
            return ApplyIncludeIds(queryable, gridFilterClause, null, context);
        }

        public int GetCountByAuthInfo(IQueryable<TEntity> queryable, IAuthInfo authInfo, string errorMessage = "")
        {
            if (queryable == null)
            {
                return 0;
            }

            if (authInfo != null && authInfo.IsAdmin)
            {
                return queryable.Count();
            }

            // Test code=
            try
            {
                return queryable.Count(GetAuthQueryExpression(queryable, authInfo));
            }
            catch (Exception ex)
            {
                try
                {
                    return queryable.Count();
                }
                catch (Exception innerEx)
                {
                    return 0;
                }
            }
        }

        public List<TypeCreator> GetTypeCreators(string clientKey)
        {
            // General data fields
            List<TypeCreator> generalTypeCreators = typeof(TEntity).GetProperties().Select(property => new TypeCreator() { Type = property.PropertyType, Name = property.Name }).ToList();

            // Additional data fields
            List<TypeCreator> additionalTypeCreators = GetAdditionalTypeCreators().Select(o => (TypeCreator)o).ToList();

            return generalTypeCreators.Concat(additionalTypeCreators).ToList();
        }

        public IEnumerable<dynamic> ExtendDataEntities(IQueryable<TEntity> queryable, List<string> fieldNames, DateTime? targetDateTime, FilterSet filterSet, out List<TypeCreator> allTypeCreators)
        {
            return ExtendDataEntities(queryable, fieldNames, out _, out allTypeCreators, true, _context, targetDateTime, filterSet);
        }

        public IQueryable<TIdentity> FindIdsWithFilters(
            string gridFilterClause,
            List<int> includeOnlyIds,
            List<int> excludeAnyIds,
            FilterSet filterSet,
            TContext context)
        {
            return ApplyFilters(gridFilterClause, includeOnlyIds, excludeAnyIds, filterSet, context).Select(r => r.Id);
        }

        public virtual SearchResult<TEntity> Search(
            List<string> fields,
            IEnumerable<ISortItem> sortItems,
            FilterSet filterSet,
            string gridFilterClause,
            List<int> includeOnlyIds,
            List<int> excludeAnyIds,
            int? skip,
            int? take,
            bool? getCount = true,
            bool? isQuickSearch = false,
            Func<IQueryable<TEntity>, IQueryable<TEntity>> filterFunc = null,
            bool? isDistinct = false)
        {
            try
            {
                int totalRecordCount = 0;
                if (getCount != false)
                {
                    totalRecordCount = GetTotalCount(filterFunc, filterSet);
                }

                // sort by customer include
                var sortIncludeFilterItem = filterSet?.FilterItems?.FirstOrDefault(p => p.FieldName == "#SortIncludeByCustomer#");
                if (sortIncludeFilterItem != null)
                {
                    filterSet = null;
                }

                var queryable = ApplyFilters(gridFilterClause, includeOnlyIds, excludeAnyIds, filterSet, fields, _context, isQuickSearch);

                if (filterFunc != null)
                {
                    queryable = filterFunc(queryable);
                }

                int totalCount = queryable.Count();

                List<string> sortItemNames = sortItems?.Select(r => r.Name).ToList();

                if (totalCount > 0)
                {
                    queryable = queryable.Sort(sortItems);

                    if (sortIncludeFilterItem != null)
                    {
                        string type = string.Empty;
                        string tableName = typeof(TEntity).Name;
                        switch (tableName)
                        {
                            case "AltsiteGrid":
                                type = "Alternate Site";
                                break;
                            case "StudentGrid":
                                type = "Student";
                                break;
                            case "TripGrid":
                                type = "Trip";
                                break;
                            case "VehicleGrid":
                                type = "Vehicle";
                                break;
                            default:
                                type = tableName;
                                break;
                        }

                        if (sortIncludeFilterItem.Operator == "Desc")
                        {
                            queryable = (from a in queryable.ToList()
                                         join b in _context.Set<RecordIDFilter>().Where(r => r.Hash == sortIncludeFilterItem.TypeHint).AsNoTracking().ToList() on new { Id = Convert.ToInt32(a.Id), Type = type, Hash = sortIncludeFilterItem.TypeHint } equals new { Id = b.RecordID, b.Type, b.Hash } into c
                                         from d in c.DefaultIfEmpty()
                                         let index = d == null ? 0 : 1
                                         orderby index, a.Id
                                         select a).AsQueryable();
                        }
                        else
                        {
                            queryable = (from a in queryable.ToList()
                                         join b in _context.Set<RecordIDFilter>().Where(r => r.Hash == sortIncludeFilterItem.TypeHint).AsNoTracking().ToList() on new { Id = Convert.ToInt32(a.Id), Type = type, Hash = sortIncludeFilterItem.TypeHint } equals new { Id = b.RecordID, b.Type, b.Hash } into c
                                         from d in c.DefaultIfEmpty()
                                         let index = d == null ? 1 : 0
                                         orderby index, a.Id
                                         select a).AsQueryable();
                        }
                    }

                    queryable = queryable.SkipTake(skip, take);

                    queryable = ApplyFields(fields, filterSet, queryable, isDistinct);
                }

                return new SearchResult<TEntity>(queryable, totalCount, totalRecordCount);
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        public virtual bool VerifyWhereClause(string gridFilterClause, List<int> excludeAnyIds)
        {
            return GetFilterRecordByWhereClause($"{gridFilterClause} AND {_verifyOnlySql}", excludeAnyIds, _context).Any();
        }

        public IQueryable<TEntity> GetFilterRecordByWhereClause(string gridFilterClause, List<int> excludeAnyIds, TContext context)
        {
            ResetHashKey();
            return ApplyFilters(gridFilterClause, null, excludeAnyIds, null, context);
        }

        protected bool IsIncludeExtendData(List<string> itemNames)
        {
            return itemNames != null && itemNames.Any(r => !EntityPropertyNames.Contains(r));
        }

        protected virtual void ResetHashKey()
        {
        }

        protected virtual IQueryable<TEntity> ApplyFilters(
            string gridFilterClause,
            List<int> includeOnlyIds,
            List<int> excludeAnyIds,
            FilterSet filterSet,
            List<string> fields,
            TContext context,
            bool? isQuickSearch = false)
        {
            return ApplyFilters(gridFilterClause, includeOnlyIds, excludeAnyIds, filterSet, context, isQuickSearch);
        }

        protected virtual Expression<Func<TEntity, bool>> GetAuthQueryExpression(IQueryable<TEntity> queryable, IAuthInfo authInfo)
        {
            return r => true;
        }

        protected virtual IQueryable<TEntity> ApplyFilters(
            string gridFilterClause,
            List<int> includeOnlyIds,
            List<int> excludeAnyIds,
            FilterSet filterSet,
            TContext context,
            bool? isQuickSearch = false)
        {
            if (GetId == null)
            {
                throw new NotImplementedException("subclass should assgin getId Func");
            }

            IQueryable<TEntity> queryable = CreateQueryable(context, filterSet);

            queryable = ApplyFilterSet(filterSet, queryable, context);

            if (excludeAnyIds != null)
            {
                queryable = ApplyExcludeIds(queryable, excludeAnyIds, context);
            }

            queryable = ApplyIncludeIds(queryable, gridFilterClause, includeOnlyIds, context, filterSet?.UDGridID);
            return queryable;
        }

        protected virtual IEnumerable<dynamic> ExtendDataEntities(
                    IQueryable<TEntity> queryable,
                    IEnumerable<string> fieldNames,
                    out Type filterType,
                    out List<TypeCreator> allTypeCreators,
                    bool isExpandoObject,
                    TContext context,
                    DateTime? targetDateTime = null,
                    FilterSet filterSet = null)
        {
            string identityName = GridExecutor.GetIdentityName<TEntity, TIdentity>();
            if (!fieldNames.Contains(identityName))
            {
                fieldNames = fieldNames.Concat(new List<string>() { identityName });
            }

            fieldNames = fieldNames.Concat(NeedField).Distinct();

            queryable = AppendNotEqualExpForVerifyRequest(filterSet, queryable);

            List<dynamic> entities = queryable.Select(IncludeAdditionalFieldNames(fieldNames)).ToList();

            // ref to RW-19708
            List<int> entityIds = entities.Select(r => (int)Dynamic.InvokeGet(r, identityName)).ToList();

            // General data fields
            List<TypeCreator> generalTypeCreators = fieldNames.Where(field => typeof(TEntity).GetProperty(field) != null).Select(field => new TypeCreator() { Type = typeof(TEntity).GetProperty(field).PropertyType, Name = field }).ToList();

            // Additional data fields
            List<TypeCreator> additionalTypeCreators = GetAdditionalTypeCreators().Where(o => fieldNames.Contains(o.Name)).Select(o => (TypeCreator)o).ToList();

            // Concat all type creators
            allTypeCreators = generalTypeCreators.Concat(additionalTypeCreators).ToList();

            filterType = isExpandoObject ? null : FindEntitiesExecutor.CreateRawEntityType("GridFilter", allTypeCreators);
            if (allTypeCreators.Count == 0)
            {
                return entities;
            }

            // Generate entity collection with general and UDF data
            List<string> additionalFieldNames = additionalTypeCreators.Select(o => o.Name).ToList();

            try
            {
                bool isExport = CallStack == ExportCallStackType.Exporter;
                if (isExpandoObject)
                {
                    var result = new List<dynamic>();
                    foreach (var entity in entities)
                    {
                        string key = Dynamic.InvokeGet(entity, identityName).ToString();
                        dynamic returnData = new ExpandoObject();
                        IDictionary<string, object> dict = (IDictionary<string, object>)returnData;
                        generalTypeCreators.ForEach(creator =>
                        {
                            if (typeof(TEntity).GetProperty(creator.Name) != null)
                            {
                                dict[creator.Name] = Dynamic.InvokeGet(entity, creator.Name);
                            }
                        });

                        if (additionalFieldNames.Count > 0)
                        {
                            SetAdditionalEntity(entities, additionalFieldNames, key, dict, targetDateTime);
                        }

                        result.Add(returnData);
                    }

                    return result;
                }

                PropertyInfo[] propertyInfos = filterType.GetProperties();
                List<string> generalTypeCreatorNames = generalTypeCreators.Select(r => r.Name).ToList();
                var tempFilterType = filterType;
                var resultBag = new ConcurrentBag<dynamic>();
                var delayedAdditionalValues = new ConcurrentBag<(string Key, PropertyInfo Property, dynamic Value)>();
                Parallel.ForEach(entities, entity =>
                {
                    var keyValue = Dynamic.InvokeGet(entity, identityName);
                    string key = keyValue.ToString();
                    dynamic returnData = Activator.CreateInstance(tempFilterType);
                    foreach (PropertyInfo propertyInfo in propertyInfos)
                    {
                        var propertyName = propertyInfo.Name;
                        if (propertyName == identityName)
                        {
                            propertyInfo.SetValue(returnData, keyValue);
                            continue;
                        }

                        if (generalTypeCreatorNames.Contains(propertyName))
                        {
                            propertyInfo.SetValue(returnData, Dynamic.InvokeGet(entity, propertyName));
                            continue;
                        }

                        if (additionalFieldNames.Contains(propertyName))
                        {
                            delayedAdditionalValues.Add((key, propertyInfo, returnData));
                        }
                    }

                    resultBag.Add(returnData);
                });

                delayedAdditionalValues.ForEach(i =>
                {
                    SetAdditionalEntity(entities, additionalFieldNames, i.Property, i.Key, i.Value, targetDateTime);
                });

                return resultBag;
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        protected virtual int GetTotalCount(
            Func<IQueryable<TEntity>, IQueryable<TEntity>> filterFunc = null,
            FilterSet filterSet = null)
        {
            var query = ApplyFilters(null, null, null, null, _context);
            if (filterFunc != null)
            {
                query = filterFunc(query);
            }

            return query.Count();
        }

        protected virtual IQueryable<TEntity> ApplyExcludeIds(IQueryable<TEntity> queryable, List<int> excludeAnyIds, TContext context)
        {
            return queryable.Where(GetIdNotContainsLambda(excludeAnyIds));
        }

        protected virtual void SetAdditionalEntity(List<dynamic> entities, IEnumerable<string> fieldNames, string identity, IDictionary<string, object> entity, DateTime? targetDateTime = null)
        {
        }

        protected virtual void SetAdditionalEntity(List<dynamic> entities, IEnumerable<string> fieldNames, PropertyInfo propertyInfo, string identity, dynamic entity, DateTime? targetDateTime = null)
        {
        }

        protected virtual IQueryable<TEntity> CreateQueryable(TContext context, FilterSet filterSet)
        {
            return context.Set<TEntity>().AsNoTracking();
        }

        protected virtual IEnumerable<string> IncludeAdditionalFieldNames(IEnumerable<string> fieldNames)
        {
            return fieldNames;
        }


        protected virtual Func<DateTime, DateTime> GetFilterClauseDateTimeConverter()
        {
            return null;
        }

        protected virtual bool HasAdditionalColumns(TExpression expression, FilterSet filterSet = null, TContext context = null)
        {
            return false;
        }

        protected virtual IQueryable<TEntity> ApplyFilterSet(FilterSet filterSet, IQueryable<TEntity> queryable, TContext context, bool isWhereClause = false)
        {
            List<FilterInfo> filterInfos = GetFilterInfos(filterSet);
            List<string> filterItemNames = filterInfos.Select(r => r.FieldName).Distinct().ToList();
            return ApplyOptimizeFilterSet(filterSet, filterItemNames, queryable, context, isWhereClause);
        }

        protected virtual IQueryable<TEntity> ApplyOptimizeFilterSet(FilterSet filterSet, List<string> filterItemNames, IQueryable<TEntity> queryable, TContext context, bool isWhereClause = false)
        {
            return queryable.Filter(filterSet, DelimitedListParser);
        }

        protected virtual IQueryable<TEntity> OptimizeApplyIncludeIds(IQueryable<TEntity> queryable, List<int> ids, TContext context)
        {
            if (ids == null)
            {
                return queryable;
            }

            return queryable.Where(GetIdContainsLambda(ids));
        }

        protected virtual string[] GetDateTimeFields()
        {
            return new string[] { };
        }

        protected virtual List<GridColumnTypeCreator> GetAdditionalTypeCreators()
        {
            return new List<GridColumnTypeCreator>();
        }

        protected List<FilterInfo> GetFilterInfos(FilterSet filterSet)
        {
            List<FilterInfo> fieldInfos = new List<FilterInfo>();
            if (filterSet == null)
            {
                return fieldInfos;
            }

            if (filterSet.FilterItems != null)
            {
                fieldInfos = filterSet.FilterItems.Select(r => new FilterInfo() { FieldName = r.FieldName, Operator = r.Operator }).ToList();
            }

            if (filterSet.FilterSets != null)
            {
                foreach (var subFilterSet in filterSet.FilterSets)
                {
                    fieldInfos.AddRange(GetFilterInfos(subFilterSet));
                }
            }

            return fieldInfos;
        }

        protected string ConvertEntityField2DBField(string expressionWhereClause, TContext context)
        {
            var persistenceMap = PersistenceMapProvider.GetPersistenceMap<TEntity>(context).PersistenceMap;
            MatchCollection matches = Regex.Matches(expressionWhereClause, @"\[([^\[\]]+)\]");
            var groups = matches.Cast<Match>().GroupBy(x => x.Value).Select(g => g.FirstOrDefault());
            foreach (Match match in groups)
            {
                if (match.Success)
                {
                    string entityField = match.Groups[1].Value;
                    string field = entityField;

                    if (persistenceMap.ContainsKey(entityField))
                    {
                        field = persistenceMap[entityField].Name;
                    }

                    expressionWhereClause = expressionWhereClause.Replace(entityField, field);
                }
            }

            return expressionWhereClause;
        }

        protected List<string> AddFilterFields(List<string> fields, FilterSet filterSet)
        {
            var searchFilterFields = filterSet.FilterItems.Select(c => c.FieldName);

            foreach (var field in searchFilterFields)
            {
                if (!fields.Contains(field))
                {
                    fields.Add(field);
                }
            }

            foreach (FilterSet fs in filterSet.FilterSets)
            {
                AddFilterFields(fields, fs);
            }

            return fields;
        }

        protected Expression<Func<TEntity, bool>> GetIdNotContainsLambda(List<int> ids)
        {
            return GetIdContainsExpression(ids, false);
        }

        protected Expression<Func<TEntity, bool>> GetIdContainsLambda(List<int> ids)
        {
            return GetIdContainsExpression(ids, true);
        }

        protected List<int> IncludeIdsHelper(string getIdSQL, string gridFilterClause, List<int> includeOnlyIds, TContext context)
        {
            List<int> ids = null;
            List<int> gridFilterIds = null;
            if (!string.IsNullOrEmpty(gridFilterClause))
            {
                if (IsStaticFilters(gridFilterClause))
                {
                    gridFilterIds = GetStaticFiltersIds(gridFilterClause, context);
                }
                else
                {
                    string whereClause = gridFilterClause;

                    if (!gridFilterClause.Contains(_verifyOnlySql))
                    {
                        var expression = SqlParserUtil.ParseExpression(gridFilterClause);
                        var filterSet = SqlParserUtil.GetFilterSetByExpression(expression, PersistenceMapProvider.GetPersistenceMap<TEntity>(context).PersistenceMap);

                        if (!GetFilterInfos(filterSet).Any(x => x.FieldName.StartsWith("Round(", StringComparison.OrdinalIgnoreCase)))
                        {
                            whereClause = GetClauseFromFilter(filterSet, context);
                        }
                    }

                    gridFilterIds = GetFilteredIds(whereClause, getIdSQL, includeOnlyIds, context);
                }
            }

            if (includeOnlyIds == null)
            {
                ids = gridFilterIds;
            }
            else if (gridFilterIds == null)
            {
                ids = includeOnlyIds;
            }
            else
            {
                ids = gridFilterIds.Where(c => includeOnlyIds.Contains(c)).ToList();
            }

            return ids;
        }

        private bool IsStaticFilters(string staticFilter)
        {
            return StaticFilters.Any(filter => filter.Name == staticFilter);
        }

        private IQueryable<TEntity> ApplyIncludeIds(IQueryable<TEntity> queryable, string gridFilterClause, List<int> includeOnlyIds, TContext context, int? udgridId = null)
        {
            var ids = includeOnlyIds;
            if (string.IsNullOrEmpty(gridFilterClause))
            {
                return OptimizeApplyIncludeIds(queryable, ids, context);
            }

            if (IsStaticFilters(gridFilterClause))
            {
                ids = IncludeIdsHelper(SQLQuery, gridFilterClause, includeOnlyIds, context);
                return OptimizeApplyIncludeIds(queryable, ids, context);
            }

            var expression = SqlParserUtil.ParseExpression(gridFilterClause, GetFilterClauseDateTimeConverter(), GetDateTimeFields());
            if (expression == null)
            {
                ids = IncludeIdsHelper(SQLQuery, gridFilterClause, includeOnlyIds, context);
            }

            expression = ProcessHasAdditionalColumnsExpression(gridFilterClause);

            Dictionary<string, IProperty> persistenceMap = null;

            var filterSet = SqlParserUtil.GetFilterSetByExpression(expression, persistenceMap);

            if (udgridId.HasValue)
            {
                filterSet.UDGridID = udgridId;
            }

            if (HasAdditionalColumns(expression, filterSet, context))
            {
                queryable = ApplyFilterSet(filterSet, queryable, context, true);
            }
            else
            {
                expression = SqlParserUtil.ParseExpression(gridFilterClause, GetFilterClauseDateTimeConverter(), GetDateTimeFields());
                ids = IncludeIdsHelper(SQLQuery, expression.ToString(), includeOnlyIds, context);
            }

            return OptimizeApplyIncludeIds(queryable, ids, context);
        }

        private TExpression ProcessHasAdditionalColumnsExpression(string gridFilterClause)
        {
            string replacement = " ";
            Regex rgx = new Regex("1=1 and", RegexOptions.IgnoreCase);

            string processedFilterClause = rgx.Replace(gridFilterClause, replacement);

            return SqlParserUtil.ParseExpression(processedFilterClause, GetFilterClauseDateTimeConverter(), GetDateTimeFields());
        }

        private Expression<Func<TEntity, bool>> GetIdContainsExpression(List<int> ids, bool contain)
        {
            List<TIdentity> containsIds = ids.Select(r => (TIdentity)Convert.ChangeType(r, typeof(TIdentity))).ToList();
            ConstantExpression values = Expression.Constant(containsIds);
            ParameterExpression parameterExpression = Expression.Parameter(typeof(TEntity));
            MemberExpression memberExp = Expression.Property(parameterExpression, (PropertyInfo)((MemberExpression)GetId.Body).Member);
            MethodInfo containsMethod = containsIds.GetType().GetMethod("Contains", new[] { typeof(TIdentity) });
            MethodCallExpression methodCallExperssion = Expression.Call(values, containsMethod, memberExp);
            Expression<Func<TEntity, bool>> lambda;
            if (contain)
            {
                lambda = Expression.Lambda<Func<TEntity, bool>>(methodCallExperssion, parameterExpression);
            }
            else
            {
                lambda = Expression.Lambda<Func<TEntity, bool>>(Expression.Not(methodCallExperssion), parameterExpression);
            }

            return lambda;
        }

        private List<int> GetFilteredIds(string gridFilterClause, string baseQuery, List<int> includeOnlyIds, TContext context)
        {
            if (!string.IsNullOrWhiteSpace(gridFilterClause))
            {
                gridFilterClause = ConvertEntityField2DBField(gridFilterClause, context);

                baseQuery = baseQuery.ToLower().Contains(" where ") ? $"{baseQuery} ({gridFilterClause})" : $"{baseQuery} where ({gridFilterClause})";
                if (IsVerify)
                {
                    baseQuery = baseQuery.Replace("select", "select top 1 ");
                }


                // DateFilterExtensions.DATE_FILTER_ISWITHIN_NAME = "IsWithin"
                // There's logic is replace the new filter expression to sql expression
                if (baseQuery.Contains(DateFilterExtensions.DATE_FILTER_ISWITHIN_NAME_FOR_EXPRESSION))
                {
                    var clientDateTime = DateTime.Now;
                    if (DateTime.TryParse(clientDateTime.ToShortDateString(), out DateTime currentDateTime))
                    {
                        var matches = SqlParserUtil.IsWithinRegForExpression.Matches(baseQuery);
                        foreach (Match match in matches)
                        {
                            var groupsCount = match.Groups.Count;
                            if (groupsCount != 3)
                            {
                                continue;
                            }

                            var exp = match.Groups[1].Value;
                            var val = match.Groups[2].Value;

                            if (val.Contains("-"))
                            {
                                baseQuery = baseQuery.Replace(exp, $"between DATEADD(DAY,{val},{"'" + currentDateTime + "'"}) and {"'" + currentDateTime + "'"}");
                            }
                            else
                            {
                                baseQuery = baseQuery.Replace(exp, $"between {"'" + currentDateTime + "'"} and DATEADD(DAY,{val},{"'" + currentDateTime + "'"})");
                            }
                        }
                    }
                }
            }

            var maxSqlIdInQueryCount = 2000;
            if (includeOnlyIds != null && includeOnlyIds.Any() && includeOnlyIds.Count < maxSqlIdInQueryCount)
            {
                baseQuery += (baseQuery.ToLower().Contains(" where ") ? " and" : string.Empty) + $" {PrimaryKeyName} in ({string.Join(",", includeOnlyIds)})";
            }

            try
            {
                List<int> ids = context.FromSqlQuery<int>(baseQuery).Distinct().ToList();
                return ids;
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        private IQueryable<TEntity> ApplyFields(List<string> fields, FilterSet filterSet, IQueryable<TEntity> queryable, bool? isDistinct = false)
        {
            var hasNeedField = NeedField != null && NeedField.Count > 0;
            var hasFields = fields != null;
            var isNotDistinct = !isDistinct.HasValue || !isDistinct.Value;
            if (hasNeedField && hasFields && isNotDistinct)
            {
                fields.AddRange(NeedField.Where(field => !fields.Contains(field)));
            }

            List<TEntity> result;

            // entity framework use select on entity will throw exception, so need a middle entity
            if (fields != null && fields.Count > 0)
            {
                var fieldsAddConditionFields = new List<string>(fields);
                if (filterSet != null)
                {
                    AddFilterFields(fieldsAddConditionFields, filterSet);
                }

                var list = queryable.Select(IncludeAdditionalFieldNames(fieldsAddConditionFields)).ToList();
                result = JsonConvert.DeserializeObject<List<TEntity>>(JsonConvert.SerializeObject(list));
            }
            else
            {
                result = queryable.ToList();
            }

            return result.AsQueryable();
        }

        private List<int> GetStaticFiltersIds(string filterName, TContext db = null)
        {
            var staticFilter = StaticFilters.FirstOrDefault(filter => filter.Name == filterName);
            if (staticFilter is SqlStaticFilter<TContext>)
            {
                var sql = (staticFilter as SqlStaticFilter<TContext>).Sql;
                sql = string.Format(sql, DateTime.Now);

                try
                {
                    return db.FromSqlQuery<int>(sql).ToList();
                }
                catch (Exception ex)
                {
                    throw;
                }
            }

            if (staticFilter is FuncStaticFilter<TContext>)
            {
                var func = (staticFilter as FuncStaticFilter<TContext>).FilterFunc;

                return func(db);
            }

            return null;
        }

        private IQueryable<TEntity> AppendNotEqualExpForVerifyRequest(FilterSet filterSet, IQueryable<TEntity> queryable)
        {
            if (filterSet?.FilterItems?.Any(item => item.FieldName == "1" && item.Operator == "NotEqualTo" && item.Value == "1") ?? false)
            {
                return queryable.Where(m => false);
            }

            return queryable;
        }

        private string GetClauseFromFilter(FilterSet filterSet, TContext context)
        {
            var convertToRawWhereClause = filterSet == null || filterSet.IsConvertToRawWhereClause;
            var convertDateTimeToLocalFormat = filterSet != null && filterSet.IsConvertDateTimeToLocalFormat;

            var filterItemNames = GetFilterInfos(filterSet).Select(r => r.FieldName).Distinct().ToList();

            // Additional data fields
            var additionalTypeCreators = GetAdditionalTypeCreators().Select(o => (TypeCreator)o).ToList();
            List<TypeCreator> generalTypeCreators = filterItemNames.Where(field => typeof(TEntity).GetProperty(field) != null).Select(field => new TypeCreator() { Type = typeof(TEntity).GetProperty(field).PropertyType, Name = field }).ToList();
            var type = FindEntitiesExecutor.CreateRawEntityType("GridFilter", generalTypeCreators);
            var filterExpressionParser = new FilterExpressionParser<TEntity>(new PropertyMap(type), DelimitedListParser);
            var expressionWhereClause = filterExpressionParser.CreateSQLWhereClause(filterSet, convertToRawWhereClause, type, convertDateTimeToLocalFormat, GetDateTimeFields());

            return expressionWhereClause;
        }
    }
    public abstract class BaseGridService<TDomainEntity> : BaseOptimizeFilterGridService<TDomainEntity, int>, IBaseGridService<TDomainEntity>
        where TDomainEntity : class, IGridEntity<int>, new()
    {
        protected BaseGridService(
            ApiDBContent context,
            IDelimitedListParser delimitedListParser)
            : base(context, delimitedListParser)
        {
        }

        protected override IQueryable<TDomainEntity> GetQueryableJoinRecordIDFilter(IQueryable<TDomainEntity> queryable, ApiDBContent context)
        {
            return queryable;
        }

        protected override IQueryable<TDomainEntity> GetQueryableExcludeRecordIDFilter(IQueryable<TDomainEntity> queryable, ApiDBContent context)
        {
            return queryable;
        }
    }

    public abstract class BaseGridService<TDomainEntity, TIdentity> : BaseGridService<TDomainEntity, ApiDBContent, TIdentity>, IBaseGridService<TDomainEntity, TIdentity>
        where TDomainEntity : class, IGridEntity<TIdentity>, new()
    {
        protected BaseGridService(
            ApiDBContent context,
            IDelimitedListParser delimitedListParser)
            : base(context, delimitedListParser)
        {
        }
    }

    public abstract class BaseOptimizeFilterGridService<TDomainEntity, TIdentity> : BaseGridService<TDomainEntity, TIdentity>
             where TDomainEntity : class, IGridEntity<TIdentity>, new()
    {
        private const int MAX_IN_GIRD_QUERY_NUMBER = 2000;

        private readonly IRecordIDFilterRepository _recordIdFilterRepository;

        protected BaseOptimizeFilterGridService(
            ApiDBContent context,
            IDelimitedListParser delimitedListParser)
            : base(context, delimitedListParser)
        {
        }

        protected abstract IQueryable<TDomainEntity> GetQueryableExcludeRecordIDFilter(IQueryable<TDomainEntity> queryable, ApiDBContent context);

        protected abstract IQueryable<TDomainEntity> GetQueryableJoinRecordIDFilter(IQueryable<TDomainEntity> queryable, ApiDBContent context);

        protected override IQueryable<TDomainEntity> ApplyOptimizeFilterSet(FilterSet filterSet, List<string> filterItemNames, IQueryable<TDomainEntity> queryable, ApiDBContent context, bool isWhereClause = false)
        {
            queryable = queryable.Filter(filterSet, DelimitedListParser);
            return queryable;
        }

        protected override IQueryable<TDomainEntity> ApplyExcludeIds(IQueryable<TDomainEntity> queryable, List<int> excludeAnyIds, ApiDBContent context)
        {
            if (excludeAnyIds.Count < MAX_IN_GIRD_QUERY_NUMBER)
            {
                return base.ApplyExcludeIds(queryable, excludeAnyIds, context);
            }
            else
            {
                return GetQueryableExcludeRecordIDFilter(queryable, context);
            }
        }

        protected override IQueryable<TDomainEntity> OptimizeApplyIncludeIds(IQueryable<TDomainEntity> queryable, List<int> ids, ApiDBContent context)
        {
            if (ids == null)
            {
                return queryable;
            }

            if (ids.Count < MAX_IN_GIRD_QUERY_NUMBER)
            {
                return base.OptimizeApplyIncludeIds(queryable, ids, context);
            }

            return GetQueryableJoinRecordIDFilter(queryable, context);
        }
    }
}